<?php

RCoreImporter::cls('html.TableControl');
require 'PHPExcel.php';

class ExcelTableControl extends TableControl {

	var $_numRows = 0;
	var $_numCols = 0;
	/**
	 *
	 * Enter description here ...
	 * @var PHPExcel
	 */
	var $objPHPExcel;

	function __construct($dtoName, $arrayViewField, &$arrayRequest, $name = "tblAdminControl", $where = '', $orderBy ='', $db = null) {
		parent::__construct($dtoName, $arrayViewField, $arrayRequest, $name, $where, $orderBy, $db);
		$this->objPHPExcel = new PHPExcel();
		// Set properties
		$this->objPHPExcel->getProperties()->setCreator("PHPExcel")
		->setLastModifiedBy("PHPExcel")
		->setTitle("PHPExcel Document")
		->setSubject("PHPExcel Document")
		->setDescription("PHPExcel document generated using PHP classes.")
		->setKeywords("office 2007 openxml php")
		->setCategory("PHPExcel file");
		$this->objPHPExcel->setActiveSheetIndex(0);
		$this->_numCols = count($this->_arrayFields);
	}

	function _getHeaderExcel($dto) {
		$dbInfo = RubikDBFactory::getDBInfo ($dto);
		$this->_numRows += 1;
		$i = 1;
		$this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, SysLanguage::_('Index'));
		$this->objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 1)->getFont()->setBold(true);
		$this->objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		foreach ( $this->_arrayFields as $key => $fieldValue ) {
			$fieldInfo = $dbInfo->getDbField ( $key );
			$this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, 1, $this->getLabel($key));
			$this->objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($i, 1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$this->objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($i, 1)->getFont()->setBold(true);
			$i++;
		}

		$this->objPHPExcel->getActiveSheet()->setTitle($this->_dtoName);
	}

	/**
	 *
	 * create table content
	 * @param $result
	 */
	function _getDataExcel($result) {
		$recordSet = $result->recordSet;
		$this->_keyField = $this->getKeyFieldName ( $this->_dto );
		$keyField = $this->_keyField;

		$dbInfo = RubikDBFactory::getDBInfo($this->_dto);
		$this->_numRows += count($recordSet);
		$number = 1;
		$j = 2;
		foreach ( $recordSet as $recordValue ) {
			$i = 1;
			$keyValue = $recordValue->$keyField;
			$this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $j, $number++);
			$this->objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			foreach ( $this->_arrayFields as $key => $fieldValue ) {
				$fieldInfo = $dbInfo->getDbField ( $key );
				$data = $this->createHtmlDataField($recordValue, $key, $fieldValue, $keyValue, $fieldInfo);
				$data = strip_tags($data);
				$type = gettype($fieldValue);
				if($type == 'object') {
					if(get_class($fieldValue) == 'BooleanHtmlFieldInfo') {
						if($fieldValue->_value == '0') {
							$data = 'No';
						}
						else {
							$data = 'Yes';
						}
					}
				}
				$this->objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($i, $j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
				$this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($i)->setWidth(15);
				$this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i++, $j, $data);
			}
			$j++;
		}
	}

	/**
	 * Create Excel table
	 *
	 * @param number $recordPerPage
	 * @param string $prefix
	 * @return binary string of excel content
	 */
	function createExcelTable($recordPerPage = 0, $prefix = "#__") {
		$indexPage = $this->_arrayRequest [$this->_page];
		if (empty ( $indexPage ))
		$indexPage = 1;

		$orderBy = '';
		if(!empty($this->_defaultOrderBy))
		$orderBy = 'ORDER BY '.$this->_defaultOrderBy;
		if ($this->_arrayRequest [$this->_orderby])
		$orderBy = 'ORDER BY ' . $this->_arrayRequest [$this->_orderby] . ' ' . $this->_arrayRequest [$this->_sort];

		$result = $this->_getDataResult ( $orderBy, $recordPerPage, $indexPage, $prefix );


		$this->_getHeaderExcel ( $this->_dto );
		$this->_getFilter($this->_dto ); // just for create some combox if need to get data
		$this->_getDataExcel ( $result );

		return PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
	}

	/**
	 *
	 * export to excel file
	 * @param string $fileName name of the excel file
	 */
	function runExport($fileName = null, $conditionalArray = array()) {
		if (!$fileName) {
			$fileName = $this->_dtoName.".xls";
		}
		if(count($conditionalArray) == 0 || empty($conditionalArray)) {
			$objWriter = $this->createExcelTable($recordPerPage, $prefix);
		}
		else {
			$objWriter = $this->createExcelTableConditional($recordPerPage, $prefix, $conditionalArray['insertNumRows'], $conditionalArray['fontSize'], $conditionalArray['text']);
		}
		header('Content-Type: application/vnd.ms-excel');
		header("Content-Disposition: attachment;filename={$fileName}");
		header('Cache-Control: max-age=0');

		$objWriter->save('php://output');
	}

	/**
	 *
	 * Create an excel table with conditional style
	 * @param int $recordPerPage: Number of records per page
	 * @param string $prefix: Prefix of databse table
	 * @param int $insertNumRows: Number of rows that will be inserted from the first row
	 * @param double $fontSize: Font size for $text
	 * @param string $text: Text content that will be put in the inserted row
	 * @return PHPExcel_Writer_IWriter
	 */
	function createExcelTableConditional($recordPerPage = 0, $prefix = "#__", $insertNumRows, $fontSize, $text) {
		$indexPage = $this->_arrayRequest [$this->_page];
		if (empty ( $indexPage ))
		$indexPage = 1;

		$orderBy = '';
		if(!empty($this->_defaultOrderBy))
		$orderBy = 'ORDER BY '.$this->_defaultOrderBy;
		if ($this->_arrayRequest [$this->_orderby])
		$orderBy = 'ORDER BY ' . $this->_arrayRequest [$this->_orderby] . ' ' . $this->_arrayRequest [$this->_sort];

		$result = $this->_getDataResult ( $orderBy, $recordPerPage, $indexPage, $prefix );


		$this->_getHeaderExcel ( $this->_dto );
		$this->_getFilter($this->_dto ); // just for create some combox if need to get data
		$this->_getDataExcel ( $result );
		$this->objPHPExcel->getActiveSheet()->insertNewRowBefore(1, $insertNumRows);
		$this->objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, 1, $this->_numCols, $insertNumRows);
		$this->objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize($fontSize);
		$this->objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->objPHPExcel->getActiveSheet()->setCellValue("A1", $text);
		$this->objPHPExcel->getActiveSheet()->insertNewRowBefore($insertNumRows + 1);
		$this->objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $insertNumRows + 1, $this->_numCols, $insertNumRows + 1);
		$this->objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(14);
		$this->objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$this->objPHPExcel->getActiveSheet()->setCellValue("A3", 'Report date: '.strftime('%B %d, %Y'));
		
		$styleArray = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,
									'color' => array('argb' => 'FFA0A0A0')),
		);
		$this->objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
		$this->objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray);
		$this->objPHPExcel->getActiveSheet()->setSelectedCellByColumnAndRow(0, 0);
		return PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
	}
}
?>